#include "db.h"
#include "sqlite3.h"
#include "sqliteinterface.h"
#include <time.h>
#include <errno.h>


//数据库初始化
int db_init(void)
{
	printf("db_init working...\n");
	//创建数据库
	int ret = OpenConnection("data.db");
	if(DB_ERROR_OK != ret)	
	{
		perror("open db error");
		return DB_ERR;
	}
	printf("open db ok\n");
	//创建温湿度信息表
	char sqlCreateTable[] = "create table tempInfo(time int primary key not null, \
							 temp int not null,humi int not null,light int not null)";
	ret = ExecSQL(sqlCreateTable);
	if(DB_ERROR_OK != ret)
	{
		printf("create table error\n");
		return DB_OK;
	}
	printf("create tempInfo table ok\n");

	return DB_OK;
}
//向数据库添加温湿度信息
//@temp:温度数据
//@humi:湿度数据
//@retval: DB_ERR 添加失败 DB_OK 添加成功
int db_insert(uint16_t  humi,uint16_t temp, uint16_t light)
{
	//printf("temp info insert...\n");	
	//删除超记录数据
	db_delete();
	//系统时间获取
	time_t now;
	struct tm * tblock;
	time(&now);
	tblock = localtime(&now);
	char * loctime = asctime(tblock);
	//添加温湿度信息动作
	char sqlinsert[1024] = {0};
	sprintf(sqlinsert,"insert into tempInfo(time,humi,temp,light) \
			values('%s','%d','%d','%d');", \
			loctime,humi,temp,light);
	int ret = ExecSQL(sqlinsert);
	if(DB_ERROR_OK != ret)
	{
		printf("temp info insert error\n");
		return DB_ERR;
	}
	//printf("temp info insert ok\n");

	return DB_OK;
}
//从数据库查询温湿度信息
//@content_db:查询内容
//@seek_begin:查询起始
//@seek_length:查询长度
//@retval: DB_ERR 查询失败 DB_OK 查询成功
int db_seek(int sign_db,int seek_begin,int seek_length)
{
	printf("temp info seek...\n");
	char sqlselect[1024] = {0};
	switch(sign_db)
	{
	case 1://查询湿度
		sprintf(sqlselect,"select humi from tempInfo order by time desc limit %d,%d;", \
				seek_begin - 1,seek_length);
		break;
	case 2://查询温度
		sprintf(sqlselect,"select temp from tempInfo order by time desc limit %d,%d;", \
				seek_begin - 1,seek_length);
		break;
	case 3://查询光照强度
		sprintf(sqlselect,"select light from tempInfo order by time desc limit %d,%d;", \
				seek_begin - 1,seek_length);
		break;
	default:
		printf("input error\r\n");
		return -1; 
	}
	int ret = GetTable(sqlselect,&pptable,&iRow,&iColumn);
	if(DB_ERROR_OK != ret)
	{
		printf("temp info seek error\n");
		return DB_ERR;
	}
	printf("temp info seek ok\n");

	return DB_OK;
}
//删除超过记录总条数数据
int db_delete(void)
{
	//printf("temp info delete...\n");
	while(1)
	{
		//数据条数校验
		char sqlCount[] = "select time from tempInfo;";
		int ret = GetTable(sqlCount,&pptable,&iRow,&iColumn);
		//printf("记录总条数%d\n",iRow);
		if(iRow < 1000) break;
		//删除动作
		char sqldelete[1024] = "delete from tempInfo where time=(select MIN(time) from tempInfo);";
		ret = ExecSQL(sqldelete);
		if(DB_ERROR_OK != ret)
		{
			printf("temp info delete error\n");
			return DB_ERR;
		}
		//printf("temp info delete ok\n");
	}
	return DB_OK;
}

